package com.apes.fn.server.productRequire;

import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface ProductRequireRepository extends CustomRepository<ProductRequire, String> {
    @Query(value = " SELECT '截至 ' || TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') || ' 为止, 您目前在新系统有【' || " +
            "        COUNT_DINGDING_UID || '】单要货单超三天未配货, 请及时处理' MESSAGE, " +
            "       A.DING_DING_USER_ID, A.CREATOR_ID, " +
            "       (SELECT ID FROM AUT_USERS WHERE ORIGIN = A.CREATOR_ID) USER_ID " +
            "  FROM V_PRODUCTREQUIRE_DINGDING_MSG A " +
            " WHERE DING_DING_USER_ID IS NOT NULL " +
            "   AND A.CREATOR_ID NOT IN " +
            "       (SELECT A.PERSON_ID " +
            "          FROM AUT_USERS A, AUT_POST_USERS B, AUT_ROLE_POSTS C " +
            "         WHERE A.ID = B.USER_ID " +
            "           AND B.POST_ID = C.POST_ID " +
            "           AND C.ROLE_ID IN ('G03', 'G01', 'G04')) ", nativeQuery = true)
    List<Map> qryOverdueProductRequire_persoon();

    @Query(value = " SELECT (SELECT '截至 ' || TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') || " +
            "                 ' 为止, 您目前在新系统有【' || SUM(COUNT_DINGDING_UID) || " +
            "                 '】单要货单超三天未配货, 请及时处理' MESSAGE " +
            "           FROM V_PRODUCTREQUIRE_DINGDING_MSG A) MESSAGE, " +
            "       D.DING_DING_USER_ID, " +
            "       (SELECT ID FROM AUT_USERS J WHERE J.ORIGIN = A.ORIGIN) USER_ID " +
            "  FROM AUT_USERS A, AUT_POST_USERS B, AUT_ROLE_POSTS C, V_GET_DINGDING_UID D " +
            " WHERE A.ID = B.USER_ID " +
            "   AND B.POST_ID = C.POST_ID " +
            "   AND A.ORIGIN = D.ID " +
            "   AND D.EMPLOYEE_STATE_ID = '000088' " +
            "   AND C.ROLE_ID IN ('G03', 'G01', 'G04') ", nativeQuery = true)
    List<Map> qryOverdueProductRequire_role();

    List<ProductRequire> findAllBySource(String source);

    @Query(value = "SELECT A.id " +
            "  FROM STOCK_PRODUCT_REQUIRE A, STOCK_PRODUCT_REQUIRE_ITEM B " +
            " WHERE A.ID = B.PRODUCT_REQUIRE_ID " +
            "   AND STATE = 'create' AND STATE != 'delete' AND ROWNUM <= 50 " +
//            "   AND QTY_REQUIRE - QTY_ASSIGNED - QTY_ENDED - ORDER_QTY > 0 " +
            "   AND A.CREATE_DATE <= SYSDATE - TO_NUMBER((SELECT VALUE FROM MAS_BUSINESS_PARAM WHERE QUERY_KEY = 'requireAutoDelete'))  ", nativeQuery = true)
    List<Map> canCancelRequire();

    @Query(value = "SELECT * FROM STOCK_PRODUCT_REQUIRE WHERE ID = :id ", nativeQuery = true)
    ProductRequire accordingIdRequire(@Param("id") String id);

    @Query(value = "" +
            "SELECT CREATOR_ID FROM SER_SERVICE_PLAN WHERE ID = (SELECT SOURCE FROM STOCK_PRODUCT_REQUIRE WHERE ID = ?1) " +
            "UNION ALL " +
            "SELECT CREATOR_ID FROM SER_LOSS_QUOTATION WHERE ID = (SELECT SOURCE FROM STOCK_PRODUCT_REQUIRE WHERE ID = ?1) ", nativeQuery = true)
    Map obtainCreator(String requireId);

    @Query(value = "SELECT I FROM ProductRequire T,ProductRequireItem I WHERE T.id = I.productRequire.id" +
            " and T.state <> :state and I.product.id = :productId and T.source = :source ")
    ProductRequireItem findStateNotAndProductId(@Param("state")String state,@Param("productId")String productId,@Param("source")String source);

    @Query(value = "" +
            "SELECT (SELECT dept_id FROM MAS_DEPOT  WHERE id =   (SELECT depot_id FROM MAS_LOCATION WHERE id = yh.LOCATION_ID)) DEPT_ID   " +
            "FROM STOCK_PRODUCT_REQUIRE yh where id=:id ", nativeQuery = true)
    String findByLocationAndById(@Param("id")String id);

    @Query(value = "" +
            "select D.Is_Want_Invoice\n" +
            "  from STOCK_PRODUCT_REQUIRE A,\n" +
            "       mas_party_role        B,\n" +
            "       mas_party             C,\n" +
            "       mas_corporate         D\n" +
            " where A.company_id = B.id\n" +
            "   AND B.PARTY_ID = C.id\n" +
            "   AND C.id = D.PARTY_ID\n" +
            "   AND A.Id =:id", nativeQuery = true)
    String findByCompanyByPartyByCorporate(@Param("id")String id);


}
